/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package modulo1.cgd;

import java.util.List;
import javax.persistence.Query;
import modulo1.cdp.Aluno;
import modulo1.cdp.Disciplina;
import modulo1.cdp.Turma;

/**
 *
 * @author Juan
 */
public class AlunoDaoJpa extends DaoJpa<Aluno> {
    
    public Aluno buscarLogon(String matricula, String senha) throws Exception {
        try {
            Query q = em.createQuery("SELECT a FROM Aluno a WHERE a.matricula LIKE '" + matricula + "' AND a.senha LIKE '" + senha + "'");
            if(q.getResultList().size() == 1)
                return (Aluno)q.getResultList().get(0);
            else
                return null;
        } catch (Exception ex) {
            throw ex;
        }
    }

    public List<Aluno> buscarNome(String nome) throws Exception {
        try {
            Query q = em.createQuery("SELECT a FROM Aluno a WHERE LOWER(a.nome) LIKE LOWER('" + nome + "%') ");
            return q.getResultList();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public List<Aluno> buscarMatriculadoTurma(Turma turma) throws Exception {
        try {
            Query q = em.createQuery("SELECT m.aluno FROM Matricula m WHERE m.turma.id = "+turma.getId());
            return q.getResultList();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public List<Aluno> buscarAptosTurma(Turma turma) throws Exception {
        try {
            List<Disciplina> prerequisitos = turma.getDisciplina().getPrerequisitos();
            if (prerequisitos.isEmpty()) {
                Query q = em.createQuery("SELECT a FROM Aluno a");
                return q.getResultList();
            } else {
                //TODO Retirar os que já estão matriculados
                //GAMBIARRA MASTER!!!                
                String ids = "";                
                for (Disciplina d : prerequisitos) {                    
                    ids = ids + d.getId() + ',';
                }
                ids = ids.substring(0, ids.length()-1);
                Query q = em.createQuery(""
                        + "SELECT m.aluno "
                        + "FROM Matricula m "
                        + "WHERE "
                        + "     m.notaFinal >= "+turma.getDisciplina().getCurso().getMediaAprovacao()
                        + "AND "
                        + "     m.frequenciaFinal >= "+turma.getDisciplina().getCurso().getMediaFrequencia()
                        + "AND "
                        + "     m.turma.disciplina.id "
                        + "		IN ("+ids+")");
                return q.getResultList();
            }
        } catch (Exception ex) {
            throw ex;
        }
    }
}
